package com.foxzzz.database;

import org.h2.jdbcx.JdbcConnectionPool;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class SqlExecutor {
    private static JdbcConnectionPool pool= null;
    private static MyDatabase database=null;
    public static void init(String path,String port,String user,String password){
        database=new MyDatabase(path,port,user,password);
        database.start();
        System.out.println("database tcp url ->"+database.getTcpUrl());
        if(pool==null) {
            pool = JdbcConnectionPool.create(database.getTcpUrl(), user, password);
            pool.setLoginTimeout(10000);//建立连接超时时间
            pool.setMaxConnections(100);//建立连接最大个数
        }
    }

    public static List<Map<String, Object>> queryBySql(String sql,Object ...args){
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        ResultSet rs=null;
        PreparedStatement preparedStatement=null;
        Connection connection=null;
        try {
            connection=pool.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            rs=preparedStatement.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                Map<String,Object> rowData = new LinkedHashMap<String,Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                    if(rs!=null)
                    rs.close();
                    if(preparedStatement!=null)
                        preparedStatement.close();
                    if(connection!=null)
                        connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return list;
    }

    public static void updateBySql(String sql,Object ...args){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection=pool.getConnection();
            preparedStatement=connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(preparedStatement!=null)
                preparedStatement.close();
                if(connection!=null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}
